Attribute VB_Name = "ContractComparison"
'Written by Jalisa Greene
'Last Modified by Jalisa Greene
'Last Modified 6/9/2014


Dim Col As Long
Dim lastRow As Integer

Sub GenContractCompare()
'**This sub will generate the EXTERNAL contract comparison worksheet for the ASR Template. The contracts are in order of when they were entered. **'
'**Current contracts are entered in first, then their corresponding new agreements if associated**'
'**New agreements that are not associated to an old contract will show at the bottom of the page (ie. master agreements that replace multiple current contracts, new containers, etc). **'

    Dim msg As Integer
    Dim lastRow As Integer
    Dim oldcontract As String
    Dim newRRContract As String
    Dim newVSContract As String
    Dim rowNum As Integer
    Dim loc As Range

    Col = RGB(165, 208, 237)
    msg = MsgBox("This file may take up to 10 minutes to generate. During this time Excel will be inaccessible, please make sure you save before you begin.", vbOKCancel)
    If msg = 1 Then 'the user clicked okay
        '**CLEAR OUT ANY OLD DATA ON THE SHEET**'
        Application.ScreenUpdating = False
        Worksheets("Contract Comparison - External").Activate
        ActiveSheet.Rows("7:2000").ClearContents
        ActiveSheet.Rows("7:2000").ClearFormats
        ActiveSheet.Rows("7:2000").RowHeight = 15
        Range("A7").Select
        
        lastRow = Worksheets("Contract Data").Range("E1099").End(xlUp).Row
        '**COPY CONTRACT ID TO THE SHEET (CURRENT CONTRACTS FIRST)**'
        
        For Each loc In Worksheets("Contract Data").Range("E10", "E" & lastRow)
            If loc.Value = "Current Contract" Then
                ActiveCell.Value = loc.Offset(, -2).Value
                ActiveCell.Offset(1).Select
            End If
        Next

        'FOR EACH CONTRACT ID, PULL THE DATA AND ANY NEW CONTRACTS THAT ARE ASSOCIATED.**'
        lastRow = Range("A1100").End(xlUp).Row
        For Each loc In Range("A7", "A" & lastRow)
            loc.Select
            If loc.Value <> "" And loc.Offset(, 2).Value = "" Then
                '**INSERT CONTRACT DATA**'
                Call InsertOldContracts(loc)
                '**INSERT NEW CONTRACT ASSOCIATED IF EXISTS**'
                Call InsertNewContracts(loc)
                '**SHADE SECTIONS BASED ON LAST COLOR USED**'
                Call Shading(loc)
            End If
        Next
        
        '**FIND LAST ROW USED, ADD NEW CONTRACTS THAT DO NOT ASSOCIATED TO ANY OLD CONTRACT**'
        lastRow = Range("A1100").End(xlUp).Row
        Range("A" & lastRow).Offset(2).Select
        Call lonelyNewContracts
        
        '**FINAL FORMATTING**'
        Range("C:J").EntireColumn.HorizontalAlignment = xlCenter
        Range("C:K").EntireColumn.VerticalAlignment = xlCenter
        Range("K:K").EntireColumn.HorizontalAlignment = xlRight
        Range("K:K").NumberFormat = "$#,##0.00"
        Range("K5").HorizontalAlignment = xlCenter

        Range("C1:C2").HorizontalAlignment = xlLeft
        lastRow = Range("C1099").End(xlUp).Row + 1
        With Range("A6", "K" & lastRow).Font
            .Name = "Helvetica"
            .Size = 14
        End With
        
    End If
End Sub

Private Sub InsertOldContracts(loc As Range)
'**SUB CALLED BY THE MAIN METHOD THAT BRINGS IN THE CURRENT CONTRACT DATA**'

    Dim dataLastRow As Integer
    
    rowNum = WorksheetFunction.Match(loc.Value, Worksheets("Contract Data").Range("C10:C1100"), 0) + 9
    'insert contract data
    ActiveCell.Offset(, 2).Value = "CURRENT"
    ActiveCell.Offset(, 2).Font.Bold = True
    ActiveCell.Offset(, 3).Value = Worksheets("Contract Data").Range("D" & rowNum).Value      'VENDOR'
    ActiveCell.Offset(, 4).Value = Worksheets("Contract Data").Range("AG" & rowNum).Value    '# OF LOCATIONS'
    ActiveCell.Offset(, 5).Value = Worksheets("Contract Data").Range("G" & rowNum).Value      'INITIAL TERM LENGTH'
    ActiveCell.Offset(, 6).Value = Worksheets("Contract Data").Range("I" & rowNum).Value       'RENEWAL TERM LENGTH'
    ActiveCell.Offset(, 7).Value = Worksheets("Contract Data").Range("J" & rowNum).Value       'TERM EXPIRATION DATE'

    ActiveCell.Offset(, 8).Value = Worksheets("Contract Data").Range("N" & rowNum).Value      'F/E SURCHARGES'
    If Worksheets("Contract Data").Range("O" & rowNum).Value <> "N/A" Then
        ActiveCell.Offset(, 9).Value = Worksheets("Contract Data").Range("O" & rowNum).Value   'RATE INCREASE DETAILS'
    Else:
        ActiveCell.Offset(, 9).Value = "No Price Controls."
    End If

    ActiveCell.EntireRow.WrapText = True
    
    ActiveCell.Offset(1).EntireRow.Insert
    ActiveCell.Offset(1).Select
    
End Sub

Private Sub InsertNewContracts(loc As Range)
'**THIS SUB IS CALLED FROM THE MAIN RUNNER THAT INSERTS ACHIEVED CONTRACT DATA FOR THOSE THAT HAVE BEEN ASSOCIATED TO AN OLD CONTRACT**'

Dim dataLastRow As Integer

    dataLastRow = Worksheets("Contract Data").Range("D1100").End(xlUp).Row
    '**SEARCH CONTRACT DATA WORKSHEET FOR IF CURRENT CONTRACT IS ASSOCIATED WITH ANY NEW CONTRACTS, IF SO ADD THEM IN**'
    With Worksheets("Contract Data").Range("AF9", "AF" & dataLastRow)
        Set c = .Find(loc.Value, LookIn:=xlValues, LookAt:=xlWhole)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                If c.Offset(, -27).Value <> "Current Contract" Then
                    rowNum = c.Row
                    'insert contract data
                    ActiveCell.Value = Worksheets("Contract Data").Range("C" & rowNum).Value
                    ActiveCell.Offset(, 2).Value = "NEW"
                    ActiveCell.Offset(, 2).Font.Bold = True
                    ActiveCell.Offset(, 3).Value = Worksheets("Contract Data").Range("D" & rowNum).Value     'VENDOR'
                    ActiveCell.Offset(, 4).Value = Worksheets("Contract Data").Range("AG" & rowNum).Value   '# OF LOCATIONS'
                    ActiveCell.Offset(, 5).Value = Worksheets("Contract Data").Range("G" & rowNum).Value     'INITAL TERM LENGTH'
                    ActiveCell.Offset(, 6).Value = Worksheets("Contract Data").Range("I" & rowNum).Value      'RENEWAL TERM LENGTH'
                    ActiveCell.Offset(, 7).Value = Worksheets("Contract Data").Range("J" & rowNum).Value      'TERM EXPIRATION DATE'
                    ActiveCell.Offset(, 8).Value = Worksheets("Contract Data").Range("N" & rowNum).Value     'F/E SURCHARGES'
                    If Worksheets("Contract Data").Range("O" & rowNum).Value <> "N/A" Then
                        ActiveCell.Offset(, 9).Value = Worksheets("Contract Data").Range("O" & rowNum).Value     'RATE INCREASE DETAILS'
                    Else:
                        ActiveCell.Offset(, 9).Value = "No Price Controls."
                    End If
                    ActiveCell.EntireRow.WrapText = True
                    
                    ActiveCell.Offset(1).EntireRow.Insert
                    ActiveCell.Offset(1).Select
                End If
                '**KEEP LOOKING THROUGH THE CONTRACT DATA SHEET UNTIL WE CANNOT FIND THE OLD CONTRACT ID BEING USED WITH ANY NEW CONTRACTS ANYWHERE ELSE**'
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With
    
End Sub

Private Sub lonelyNewContracts()
'**THIS SUB IS CALLED FROM THE MAIN METHOD TO INSERT NEW CONTRACTS THAT HAVEN'T BEEN ASSOCIATED TO ANY OLD CONTRACTS**'


    Dim dataLastRow As Integer
    Dim aCell As Range
    Dim loc As Range
    
    Set loc = ActiveCell
    dataLastRow = Worksheets("Contract Data").Range("E1100").End(xlUp).Row
    For Each aCell In Worksheets("Contract Data").Range("C10", "C" & dataLastRow)
        If aCell.Value <> "" And aCell.Offset(, 2).Value <> "Current Contract" And aCell.Offset(, 29).Value = "" Then
            'insert contract data
            ActiveCell.Value = aCell.Value
            ActiveCell.Offset(, 2).Value = "NEW"
            ActiveCell.Offset(, 2).Font.Bold = True
            ActiveCell.Offset(, 3).Value = Worksheets("Contract Data").Range("D" & aCell.Row).Value
            ActiveCell.Offset(, 4).Value = Worksheets("Contract Data").Range("AG" & aCell.Row).Value
            ActiveCell.Offset(, 5).Value = Worksheets("Contract Data").Range("G" & aCell.Row).Value
            ActiveCell.Offset(, 6).Value = Worksheets("Contract Data").Range("I" & aCell.Row).Value
            ActiveCell.Offset(, 7).Value = Worksheets("Contract Data").Range("J" & aCell.Row).Value
            ActiveCell.Offset(, 8).Value = Worksheets("Contract Data").Range("N" & aCell.Row).Value
            If Worksheets("Contract Data").Range("O" & aCell.Row).Value <> "N/A" Then
                ActiveCell.Offset(, 9).Value = Worksheets("Contract Data").Range("O" & aCell.Row).Value
            Else:
                ActiveCell.Offset(, 9).Value = "No Price Controls."
            End If

            ActiveCell.EntireRow.WrapText = True
            
            ActiveCell.Offset(1).EntireRow.Insert
            ActiveCell.Offset(1).Select
            Call Shading(loc)
        End If
    Next
End Sub


Private Sub Shading(loc As Range)
'**SHADE THE SPECIFIC AREA BASED ON THE LAST COLOR USED**'

Dim marker As Range
    Set marker = ActiveCell.Offset(-1)
    Range(loc, "K" & marker.Row).Select
    Selection.Interior.Color = Col
    Call ColorSwitch '**changes the color being used**'
    Range(loc, marker).Select
        With Selection
            .EntireRow.RowHeight = 49
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
        End With
    
End Sub

Private Sub ColorSwitch()
    '**IF WE ARE ALREADY USING BLUE, SWITCH TO WHITE**'
    If Col = RGB(165, 208, 237) Then
            Col = RGB(255, 255, 255)
    Else:
    'ELSE WE ARE ALREADY USING WHITE, SWITCH TO BLUE**'
        Col = RGB(165, 208, 237)
    End If
End Sub







'Written by Jalisa Greene
'Last Modified 2/14/2014

Sub GenInternalContracts()
    Dim msg As Integer
    Dim lastRow As Integer
    Dim oldcontract As String
    Dim newRRContract As String
    Dim newVSContract As String
    Dim rowNum As Integer
    

    Col = RGB(225, 225, 225)
    msg = MsgBox("This file may take up to 10 minutes to generate. During this time Excel will be inaccessible, please make sure you save before you begin.", vbOKCancel)
    If msg = 1 Then 'the user clicked okay
        Application.ScreenUpdating = False
        Worksheets("Contract Comparison - Internal").Activate
        ActiveSheet.Rows("11:2000").ClearContents
        ActiveSheet.Rows("11:2000").ClearFormats
        ActiveSheet.Rows("11:2000").RowHeight = 15
        Range("A11").Select
        lastRow = Worksheets("Contract Data").Range("D1099").End(xlUp).Row + 1
        'copy location id values for those who have new contracts proposed'
        Dim loc As Range
        For Each loc In Worksheets("Contract Data").Range("A10", "A" & lastRow)
            If Worksheets("Contract Data").Range("E" & loc.Row).Value = "Current Contract" Then
                 ActiveCell.Value = Worksheets("Contract Data").Range("C" & loc.Row).Value
                 ActiveCell.Offset(1).Select
            End If
        Next

        'copy  id values for new contracts that aren't replacing an old one'
        For Each loc In Worksheets("Contract Data").Range("A10", "A" & lastRow)
            If Worksheets("Contract Data").Range("E" & loc.Row).Value <> "Current Contract" And Worksheets("Contract Data").Range("AF" & loc.Row).Value = "" Then
                ActiveCell.Value = Worksheets("Contract Data").Range("C" & loc.Row).Value
                ActiveCell.Offset(1).Select
            End If
        Next
        
        'insert data
        lastRow = Range("A1099").End(xlUp).Row
        For Each loc In Range("A11", "A" & lastRow)
                loc.Select
                rowNum = WorksheetFunction.Match(loc.Value, Worksheets("Contract Data").Range("C:C"), 0)
                If Worksheets("Contract Data").Range("E" & rowNum).Value = "Current Contract" Then
                    'Insert Current Contract - Container 1'
                    Call InsertOldContractInternal(rowNum)
                    'Insert New Contracts that replace this old one'
                    Call InsertNewContractsInternal(loc, rowNum)
                Else:
                    Call InsertlonelyInternal(rowNum)
                End If
        Next
        
    End If
End Sub

Private Sub InsertOldContractInternal(rowNum As Integer)
    Range("C" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("D" & rowNum).Value
    Range("D" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("Q" & rowNum).Value
    Range("E" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("F" & rowNum).Value
    Range("F" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("G" & rowNum).Value
    Range("G" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("H" & rowNum).Value
    Range("H" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("I" & rowNum).Value
    Range("I" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("J" & rowNum).Value
    Range("J" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("K" & rowNum).Value
    Range("K" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("L" & rowNum).Value
    Range("L" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("M" & rowNum).Value
    Range("M" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("N" & rowNum).Value
    Range("N" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("O" & rowNum).Value
    Range("O" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("P" & rowNum).Value
    Range("P" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("AE" & rowNum).Value
End Sub

Private Sub InsertNewContractsInternal(loc As Range, rowNum As Integer)
    dataLastRow = Worksheets("Contract Data").Range("D1100").End(xlUp).Row
    With Worksheets("Contract Data").Range("AF9", "AF" & dataLastRow)
        Set c = .Find(loc.Value, LookIn:=xlValues, LookAt:=xlWhole)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                If c.Offset(, -27).Value = "Rate Reductions" Then
                    rowNum = c.Row
                    'RATE REDUCTION VALUES
                    Range("R" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("D" & rowNum).Value
                    Range("S" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("Q" & rowNum).Value
                    Range("T" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("AG" & rowNum).Value
                    Range("U" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("G" & rowNum).Value
                    Range("V" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("I" & rowNum).Value
                    Range("W" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("N" & rowNum).Value
                    Range("X" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("O" & rowNum).Value
                    Range("Y" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("P" & rowNum).Value
                    Range("Z" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("Z" & rowNum).Value
                    Range("AA" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("AE" & rowNum).Value
                ElseIf c.Offset(, -27).Value = "Vendor Switch" Then
                    rowNum = c.Row
                    'VENDOR SWITCH VALUES
                    Range("AC" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("D" & rowNum).Value
                    Range("AD" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("Q" & rowNum).Value
                    Range("AE" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("AG" & rowNum).Value
                    Range("AF" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("G" & rowNum).Value
                    Range("AG" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("I" & rowNum).Value
                    Range("AH" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("N" & rowNum).Value
                    Range("AI" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("O" & rowNum).Value
                    Range("AJ" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("P" & rowNum).Value
                    Range("AK" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("Z" & rowNum).Value
                    Range("AL" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("AE" & rowNum).Value
                ElseIf c.Offset(, -27).Value = "Service Standardizations" Then
                    rowNum = c.Row
                    'SERVICE STANDARDIZATION VALUES
                    Range("AN" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("D" & rowNum).Value
                    Range("AO" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("Q" & rowNum).Value
                    Range("AP" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("R" & rowNum).Value
                    Range("AQ" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("S" & rowNum).Value
                    Range("AR" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("T" & rowNum).Value
                    Range("AS" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("U" & rowNum).Value
                    Range("AT" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("Z" & rowNum).Value
                    Range("AU" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("AE" & rowNum).Value
                End If
            Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With
End Sub

Private Sub InsertlonelyInternal(rowNum As Integer)
    If Worksheets("Contract Data").Range("E" & rowNum).Value = "Rate Reductions" Then
        'RATE REDUCTION VALUES
        Range("R" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("D" & rowNum).Value
        Range("S" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("Q" & rowNum).Value
        Range("T" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("AG" & rowNum).Value
        Range("U" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("G" & rowNum).Value
        Range("V" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("I" & rowNum).Value
        Range("W" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("N" & rowNum).Value
        Range("X" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("O" & rowNum).Value
        Range("Y" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("P" & rowNum).Value
        Range("Z" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("Z" & rowNum).Value
        Range("AA" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("AE" & rowNum).Value
    ElseIf Worksheets("Contract Data").Range("E" & rowNum).Value = "Vendor Switch" Then
        'VENDOR SWITCH VALUES
        Range("AC" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("D" & rowNum).Value
        Range("AD" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("Q" & rowNum).Value
        Range("AE" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("AG" & rowNum).Value
        Range("AF" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("G" & rowNum).Value
        Range("AG" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("I" & rowNum).Value
        Range("AH" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("N" & rowNum).Value
        Range("AI" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("O" & rowNum).Value
        Range("AJ" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("P" & rowNum).Value
        Range("AK" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("Z" & rowNum).Value
        Range("AL" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("AE" & rowNum).Value
    ElseIf Worksheets("Contract Data").Range("E" & rowNum).Value = "Service Standardizations" Then
        'SERVICE STANDARDIZATION VALUES
        Range("AN" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("D" & rowNum).Value
        Range("AO" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("Q" & rowNum).Value
        Range("AP" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("R" & rowNum).Value
        Range("AQ" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("S" & rowNum).Value
        Range("AR" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("T" & rowNum).Value
        Range("AS" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("U" & rowNum).Value
        Range("AT" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("Z" & rowNum).Value
        Range("AU" & ActiveCell.Row).Value = Worksheets("Contract Data").Range("AE" & rowNum).Value
    End If
End Sub


